package cn.tx.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import cn.tx.business.OrderProduct;
import cn.tx.dao.OrderModelDao;
import cn.tx.model.OrderModel;
import cn.tx.query.OrderModelQuery;
import cn.tx.query.QueryOrderProduct;

public class OrderModelDaoImpl extends BaseDaoImpl<OrderModel, OrderModelQuery> implements OrderModelDao {

	@Override
	public String createHql(OrderModelQuery q) {
		String hql = "from OrderModel e where 1=1 ";
		String condition = this.createConditionHql(q);
		hql = hql + condition + "order by e.orderId desc";
		return hql;
	}

	@Override
	public String createHqlCount(OrderModelQuery q) {
		String hql = "select count(e) from OrderModel e where 1=1 ";
		//StringUtils.isNotBlank判断当前的字符串是不是空串和null
		String condition = this.createConditionHql(q);
		hql = hql + condition;
		return hql;
	}

	@Override
	public String createConditionHql(OrderModelQuery q) {
		String hql = "";
		if(q.getOrderState() != null){
			hql = hql + "and e.orderState = :orderState ";
		}
		
		if(StringUtils.isNotBlank(q.getOrderNum())){
			hql = hql + "and e.orderNum like :orderNum ";
		}
		if(StringUtils.isNotBlank(q.getOrderCreaterName())){
			hql = hql + "and e.orderCreater.name like :orderCreaterName ";
		}
		if(q.getTotalNumMin() != null){
			hql = hql + "and e.totalNum >= :totalNumMin ";
		}
		if(q.getOrderTypes() != null){
			hql = hql + "and e.orderType in :orderTypes ";
		}
		
		if(q.getTotalNumMax() != null){
			hql = hql + "and e.totalNum <= :totalNumMax ";
		}
		
		if(q.getCreateTimeStart()!= null){
			hql = hql + "and e.createTime >= :createTimeStart ";
		}
		if(q.getCreateTimeEnd()!= null){
			hql = hql + "and e.createTime <= :createTimeEnd ";
		}
		
		if(q.getTotalPriceMin() != null){
			hql = hql + "and e.totalPrice >= :totalPriceMin ";
		}
		if(q.getTotalPriceMax() != null){
			hql = hql + "and e.totalPrice <= :totalPriceMax ";
		}
		if(q.getTotalPriceMax() != null){
			hql = hql + "and e.totalPrice <= :totalPriceMax ";
		}
		if(q.getCompleterId() != null){
			hql = hql + "and e.orderCompleter.empId = :completerId ";
		}
		if(StringUtils.isNotBlank(q.getCompleterName())){
			hql = hql + "and e.orderCompleter.name like :completerName ";
		}
		
		
		
		
		return hql;
	}

	/*@Override
	public List<OrderProduct> staOrderProduct(final QueryOrderProduct qop) {
		List<OrderProduct> opList = new ArrayList<OrderProduct>();
		String hql = "select o.product.productId, o.product.name, sum(o.detailNum) from OrderModel e, OrderDetail o where e.orderId = o.orderModel.orderId  ";
		if(qop.getOrderType() != null){
			hql = hql + "and e.orderType = :orderType ";
		}
		
		if(qop.getSupplierId() != null){
			hql = hql + "and e.supplier.supplierId = :supplierId ";
		}
		if(qop.getStartTime() != null){
			hql = hql + "and e.createTime >= :startTime ";
		}
		if(qop.getEndTime() != null){
			hql = hql + "and e.createTime <= :endTime ";
		}
		hql = hql + "group by o.product.productId, o.product.name";
		final String hql1 = hql;
		System.out.println(hql1);
		 List<Object[]> list = this.getHibernateTemplate().execute(new HibernateCallback<List<Object[]>>() {
			
			@Override
			public List<Object[]> doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery(hql1);
				if(qop.getOrderType() != null){
					query.setParameter("orderType", qop.getOrderType());
				}
				
				if(qop.getSupplierId() != null){
					query.setParameter("supplierId", qop.getSupplierId());
				}
				if(qop.getStartTime() != null){
					query.setParameter("startTime", qop.getStartTime());
				}
				if(qop.getEndTime() != null){
					query.setParameter("endTime", qop.getEndTime());
				}
				return query.list();
			}
		});
		for(Object[] obj : list){
			OrderProduct op = new OrderProduct();
			op.setProductId((Integer)obj[0]);
			op.setProductName((String)obj[1]);
			op.setProductNum((Integer)obj[2]);
			opList.add(op);
		}
		 
		 
		return opList;
	}*/


	@Override
	public List<OrderProduct> staOrderProduct(final QueryOrderProduct qop) {
		
		String hql = "select new cn.tx.business.OrderProduct (o.product.productId, o.product.name, sum(o.detailNum)) from OrderModel e, OrderDetail o where e.orderId = o.orderModel.orderId  ";
		if(qop.getOrderType() != null){
			hql = hql + "and e.orderType = :orderType ";
		}
		
		if(qop.getSupplierId() != null){
			hql = hql + "and e.supplier.supplierId = :supplierId ";
		}
		if(qop.getStartTime() != null){
			hql = hql + "and e.createTime >= :startTime ";
		}
		if(qop.getEndTime() != null){
			hql = hql + "and e.createTime <= :endTime ";
		}
		hql = hql + "group by o.product.productId, o.product.name";
		final String hql1 = hql;
		System.out.println(hql1);
		List<OrderProduct> list = this.getHibernateTemplate().execute(new HibernateCallback<List<OrderProduct>>() {
			
			@Override
			public List<OrderProduct> doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery(hql1);
				if(qop.getOrderType() != null){
					query.setParameter("orderType", qop.getOrderType());
				}
				
				if(qop.getSupplierId() != null){
					query.setParameter("supplierId", qop.getSupplierId());
				}
				if(qop.getStartTime() != null){
					query.setParameter("startTime", qop.getStartTime());
				}
				if(qop.getEndTime() != null){
					query.setParameter("endTime", qop.getEndTime());
				}
				return query.list();
			}
		});
		
		 
		 
		return list;
	}

}
